# -*- coding: utf8 -*-
import sqlite3
import os
import os.path
import string

syspath = "../"


class Import:
    def __init__(self):
        pass

    @staticmethod
    #category_name,
    def get_category_id(f_parameters):
        category_name = f_parameters['CATEGORY_NAME']
        classification_id = f_parameters['CLASSIFICATION_ID']
        path = syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path + "DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()
        category_name = category_name.strip(" ").replace("\n","").replace("\r","").replace("\t","")
        if category_name:
            #намираме всички категории, имена
            c.execute("""select count(id)
                       from categories
                       where trim(name) = trim('""" + category_name + """') and
                             classification_id = """ + str(classification_id), {})
            row = c.fetchone()
            if row[0] == 0:
                #ако нямаме такава категория я създаваме и връщаме id
                c.execute('SELECT max(id) + 1 from categories', {})
                row = c.fetchone()
                new_id = row[0]
                if not new_id:
                    new_id = 1

                rows = [(new_id, category_name, classification_id)]
                c.executemany('insert into categories values (?,?,?)', rows)
                conn.commit()

                return new_id
            else:
                #ако имаме такава категория връщаме id
                c.execute("""select id
                            from categories
                            where trim(name) = trim('""" + category_name + """') and
                                  classification_id = """ + str(classification_id), {})
                row = c.fetchone()
                return row[0]

    @staticmethod
    def import_word(f_parameters):
        word_row = f_parameters['WORD_ROW']
        print word_row
        classification_id = f_parameters['CLASSIFICATION_ID']
        has_transcription = f_parameters['HAS_TRANSCRIPTION']
        path = syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path + "DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        cols = string.split(word_row, '|')
        if len(cols) > 0 and word_row != '' and word_row is not None:
            c.execute("""select count(id)
                         from words
                         where word_file_id = trim('""" + cols[0] + """') and
                               classification_id = """ + str(classification_id), {})
            row = c.fetchone()
            rec_id = None
            cat_start_pos = None
            if row[0] == 0:
                #ако думата не е въведена (по word_id)
                c.execute('SELECT max(id) + 1 from words', {})
                row = c.fetchone()
                rec_id = row[0]
                if not rec_id:
                    rec_id = 1

                rows = []
                #print str(len(cols))
                if has_transcription:
                    #rows.append((rec_id, classification_id, cols[1], cols[2], cols[3], cols[4], cols[0],
                    #            '1900-01-01 00:00:00', 0))
                    c.execute("insert into words values (?,?,?,?,?,?,?,?,?)", (rec_id, classification_id, cols[1], cols[2], cols[3], cols[4], cols[0],'1900-01-01 00:00:00', 0))
                    cat_start_pos = 5

                else:
                    #rows.append((rec_id, classification_id, cols[1], cols[2], '', cols[3], cols[0],
                    #             '1900-01-01 00:00:00', 0))
                    c.execute("insert into words values (?,?,?,?,?,?,?,?,?)", (rec_id, classification_id, cols[1], cols[2], '', cols[3], cols[0],'1900-01-01 00:00:00', 0))

                    cat_start_pos = 4

                #c.executemany('insert into words values (?,?,?,?,?,?,?,?,?)', rows)
                conn.commit()

            else:
            #ако я има
                c.execute("""select id
                             from words
                             where word_file_id = trim('""" + cols[0] + """') and
                                   classification_id = """ + str(classification_id), {})
                row = c.fetchone()
                rec_id = row[0]
                if has_transcription:
                    c.execute("""update words
                                 set classification_id = ?, create_date = ?,
                                     word = ?, word_tanscr = ?, word_trans = ?, word_file_id =?
                                 where id = """ + str(rec_id),
                              (classification_id, cols[1], cols[2], cols[3], cols[4], cols[0]))
                    cat_start_pos = 5
                else:
                    c.execute("""update words
                                 set classification_id = ?, create_date = ?,
                                     word = ?, word_tanscr = ?, word_trans = ?, word_file_id =?
                                where id = """ + str(rec_id),
                              (classification_id, cols[1], cols[2], '', cols[3], cols[0]))
                    cat_start_pos = 4

                    #изтриваме старите категории ако има
                c.execute("""delete from words_to_categories where word_id = """ + str(rec_id), ())
                conn.commit()

            #свързваме с категориите
            cats = []
            c.execute('SELECT max(id) + 1 from words_to_categories', {})
            row = c.fetchone()
            w_rec_id = row[0]
            if not w_rec_id:
                w_rec_id = 1
            for i in range(cat_start_pos, len(cols)):
                cat_id = Import.get_category_id({'CATEGORY_NAME': cols[i], 'CLASSIFICATION_ID': classification_id,
                                                 'PATH': f_parameters['PATH']})
                if cat_id is not None and cat_id != 0:
                    cats.append((w_rec_id, rec_id, cat_id))
                    w_rec_id += 1

            c.executemany('insert into words_to_categories values (?,?,?)', cats)
            conn.commit()

    @staticmethod
    def import_classification(f_parameters):
        classification_id = f_parameters['CLASSIFICATION_ID']
        path = syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']
        conn = sqlite3.connect(path + "DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        c.execute("""select import_file_name, has_transcription
                     from classifications
                     where id = """ + str(classification_id))
        row = c.fetchone()

        import_file_name = path + "Import/" + row[0]
        has_transcription = row[1]
        ####
        f = open(import_file_name, "rt")

        #за всяка дума
        for line in f.readlines():
            #създаваме думата
            Import.import_word({'WORD_ROW': line, 'CLASSIFICATION_ID': classification_id,
                                'HAS_TRANSCRIPTION': has_transcription, 'PATH': f_parameters['PATH']})

    @staticmethod
    def import_classifications(f_parameters):
        path = syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']
        conn = sqlite3.connect(path + "DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        #намираме всички категории, имена 
        c.execute("""select id, language_id, last_version
                     from classifications""")

        #за всяка класификация
        os.chdir(path + "/Import")
        while True:
            row = c.fetchone()
            if row is None:
                break

            #проверка дали файлът съществува
            if os.path.isfile(path + "Import/" + row[2]):
            #извикваме import
                Import.import_classification({'CLASSIFICATION_ID': row[0], 'PATH': f_parameters['PATH']})

        return 0

if __name__ == "__main__":
    Import.import_classification({'CLASSIFICATION_ID': 3, 'PATH': ''})
    Import.import_classification({'CLASSIFICATION_ID': 6, 'PATH': ''})
    Import.import_classification({'CLASSIFICATION_ID': 9, 'PATH': ''})
    print 'END'
